package com.wulis.common.utils;

import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.wulis.common.annotation.EnableExport;
import com.wulis.common.annotation.EnableExportField;
import com.wulis.common.annotation.ImportIndex;
import com.wulis.common.constant.GlobalConstant;
import com.wulis.common.enums.ColorEnum;
import com.wulis.common.enums.ExcelExtEnum;
import com.wulis.common.exception.BusinessException;
import com.wulis.common.model.dto.SheetDataDto;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.*;

import static com.wulis.common.constant.GlobalConstant.*;
import static com.wulis.common.enums.ExcelExtEnum.XLS;
import static com.wulis.common.enums.ExcelExtEnum.XLSX;
import static com.wulis.common.model.enums.HttpStatusEnum.ERROR;
import static org.apache.poi.ss.usermodel.FillPatternType.SOLID_FOREGROUND;

/**
 * @author wuligao
 * @date 日期：2024年02月04日
 * @description : excel导入导出工具类
 */
@Slf4j
public class ExcelUtil {

    private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();

    static {
        NUMBER_FORMAT.setGroupingUsed(false);
    }



    /**
     * 导出Excel文件
     * @param response 响应对象
     * @param list 要导出的数据列表
     * @param clazz 数据类
     * @param exportTitle 导出标题
     * @param extEnum 扩展类型
     */
    public static void exportExcel(HttpServletResponse response,List<?> list, Class<?> clazz, String exportTitle, ExcelExtEnum extEnum){
        if(extEnum == null || XLSX.equals(extEnum)){
            // 导出xlsx文件
            exportXlsxExcel(response, list, clazz, exportTitle);
            return;
        }
        if( XLS.equals(extEnum)){
            // 导出.xls格式文件
            exportXlsExcel(response, list, clazz,exportTitle);
            return;
        }
        log.error("暂不支持的文件类型");
        throw new BusinessException(ERROR);
    }


    /**
     * 导出多sheet Excel
     * @param response 响应对象
     * @param sheetDataList 数据列表
     * @param fileName 文件名
     * @param format Excel格式
     */
    public static void exportMultiSheetExcel(HttpServletResponse response, List<SheetDataDto<?>> sheetDataList, String fileName, ExcelExtEnum format) {
        if(CollectionUtils.isEmpty(sheetDataList)){
            log.error("导出数据不能为空");
            throw new BusinessException(ERROR);
        }
        // 创建ExcelWriter对象
        ExcelWriter writer = cn.hutool.poi.excel.ExcelUtil.getWriter(true);
        // 重命名工作表
        writer.renameSheet(0, sheetDataList.get(0).getSheetName());
        for (SheetDataDto<?> sheetData : sheetDataList) {
            // 切换到下一个sheet
            writer.setSheet(sheetData.getSheetName());
            // 添加标题别名
            addHeaderAlias(sheetData.getClazz(), writer);
            // 添加数据到当前sheet
            writer.write(sheetData.getData(), true);
            // 设置自动列宽
            setAutoColumnSize(writer.getSheet(), writer.getColumnCount() - 1);
        }
        // 设置content—type
        response.setContentType(getContentType(format));
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            log.error("文件名编码发生异常",e);
            throw new RuntimeException(e);
        }
        // 设置响应头
        response.setHeader(GlobalConstant.RESPONSE_HEADER_NAME, RESPONSE_HEADER_VALUE + fileName + format.getExt());
        // 写入文件
        writerAndClose(response,writer);
    }


    /**
     * 将Excel转换为对象集合，支持多sheet导入解析，每个sheet可能对应不同对象
     * @param file Excel 文件
     * @param clazzMap sheet页对应的对象类型映射，键是sheet页索引，值是对应的目标对象类型
     * @return 对象集合，每个sheet对应一个对象集合
     */
    public static Map<Integer, List<?>> parseMultiSheetExcel(MultipartFile file, Map<Integer, Class<?>> clazzMap){
        return parseMultiSheetExcel(file, clazzMap, 1);
    }

    /**
     * 将Excel转换为对象集合，支持多sheet导入解析，每个sheet可能对应不同对象
     * @param file Excel 文件
     * @param clazzMap sheet页对应的对象类型映射，键是sheet页索引，值是对应的目标对象类型
     * @param dataRow 数据起始行，从0开始
     * @return 对象集合，每个sheet对应一个对象集合
     */
    public static Map<Integer, List<?>> parseMultiSheetExcel(MultipartFile file, Map<Integer, Class<?>> clazzMap, Integer dataRow) {
        Map<Integer, List<?>> resultMap = new HashMap<>();
        // 获取属性名称map
        Map<Integer, Map<Integer, String>> propertyNamesMap = getPropertyNamesMap(clazzMap);
        // 获取excel对象
        Workbook workbook = getWorkbook(file);
        for (Map.Entry<Integer, Class<?>> entry : clazzMap.entrySet()) {
            int sheetIndex = entry.getKey();
            Class<?> clazz = entry.getValue();
            // 获取当前sheet表对象
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            // 获取表的最大行数
            int lastRowNum = sheet.getLastRowNum();
            List<Object> list = new ArrayList<>();
            for (int y = dataRow; y <= lastRowNum; y++) {
                // 获取行数
                Row row = sheet.getRow(y);
                Map<Integer, String> propertyNameMap = propertyNamesMap.get(sheetIndex);
                Object obj = convertObject(row, propertyNameMap, clazz);
                list.add(obj);
            }
            resultMap.put(sheetIndex, list);
        }
        return resultMap;
    }


    /**
     * 根据指定的Excel格式获取内容类型。
     *
     * @param format Excel格式枚举
     * @return 内容类型
     * @throws IllegalArgumentException 如果不支持的Excel格式被指定
     */
    private static String getContentType(ExcelExtEnum format) {
        switch (format) {
            case XLS:
                return RESPONSE_CONTENT_TYPE_XLS;
            case XLSX:
                return RESPONSE_CONTENT_TYPE_XLSX;
            default:
                throw new IllegalArgumentException("Unsupported Excel format: " + format.name());
        }
    }



    /**
     * 导出.xlsx格式表格
     * @param response 响应对象
     * @param list 要导出的数据列表
     * @param clazz 要导出的数据类型
     * @param exportTitle 导出标题
     */
    private static void exportXlsxExcel(HttpServletResponse response,List<?> list, Class<?> clazz, String exportTitle){
        // 导出.xlsx格式表格
        ExcelWriter writer = cn.hutool.poi.excel.ExcelUtil.getWriter(true);
        // 自添加标题别名
        addHeaderAlias(clazz,writer);

        // 添加数据
        writer.write(list, true);
        // 设置自动列宽
        setAutoColumnSize(writer.getSheet(),writer.getColumnCount()-1);
        // 设置content—type
        response.setContentType(RESPONSE_CONTENT_TYPE_XLS);
        // 获取文件名
        String fileName = getFileName(clazz,exportTitle);
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        //Content-disposition是MIME协议的扩展，MIME协议指示MIME用户代理如何显示附加的文件。
        response.setHeader(RESPONSE_HEADER_NAME, RESPONSE_HEADER_VALUE + fileName + XLSX.getExt());
        // 写入文件
        writerAndClose(response,writer);
    }

    /**
     * 将ExcelWriter对象中的数据刷新到HttpServletResponse的输出流中，并关闭输出流和ExcelWriter对象
     *
     * @param response HttpServletResponse对象
     * @param writer    ExcelWriter对象
     */
    private static void writerAndClose(HttpServletResponse response,ExcelWriter writer){
        // 获取输出流
        ServletOutputStream outputStream;
        try {
            outputStream = response.getOutputStream();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        // 将Writer刷新到OutPut
        writer.flush(outputStream, true);
        // 关闭输出流
        try {
            outputStream.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        // 关闭ExcelWriter对象
        writer.close();
    }



    /**
     * 获取属性名称的映射关系
     * @param clazzMap sheet页对应的对象类型映射，键是sheet页索引，值是对应的目标对象类型
     * @return 属性名称的映射关系，外层Map的键是sheet页索引，内层Map的键是属性在Excel中的列索引，值是属性的名称
     */
    private static Map<Integer, Map<Integer, String>> getPropertyNamesMap(Map<Integer, Class<?>> clazzMap) {
        Map<Integer, Map<Integer, String>> resultMap = new HashMap<>();
        for (Map.Entry<Integer, Class<?>> entry : clazzMap.entrySet()) {
            int sheetIndex = entry.getKey();
            Class<?> clazz = entry.getValue();
            Map<Integer, String> propertyNameMap = getPropertyNameMap(clazz);
            resultMap.put(sheetIndex, propertyNameMap);
        }
        return resultMap;
    }



    private static String getFileName(Class<?> clazz,String exportTile){
        EnableExport annotation = clazz.getAnnotation(EnableExport.class);
        if(annotation == null){
            throw new BusinessException(ERROR,"当前实体类不支持导出,不存在@EnableExport注解");
        }
        String fileName = annotation.fileName();
        if(StringUtils.isBlank(fileName)){
            fileName = String.valueOf(System.currentTimeMillis());
        }
        if(!StringUtils.isBlank(exportTile)){
            fileName = exportTile;
        }
        if(StringUtils.isBlank(fileName)){
            // 文件名字为空则默认文件名
            fileName = String.valueOf(System.currentTimeMillis());;
        }
        return fileName;
    }

    /**
     * 添加数据列别名
     */
    private static void addHeaderAlias(Class<?> clazz,ExcelWriter writer){
        // 获取声明的字段信息
        Field[] declaredFields = clazz.getDeclaredFields();
        if(declaredFields.length == 0){
            throw new BusinessException(ERROR,"数据导出实体类没有定义属性,导出失败");
        }
        // 注解数量
        int annotationCount = 0;
        // 列下标
        int columnIndex=0;
        for(Field field : declaredFields){
            // 获取数据导出注解信息
            EnableExportField annotation = field.getAnnotation(EnableExportField.class);
            if(annotation != null){
                // 添加列名信息
                writer.addHeaderAlias(field.getName(),annotation.colName());
                // 设置列宽度
                writer.setColumnWidth(columnIndex,annotation.colWidth());
                // 设置列样式
                annotationCount ++;
            }
        }
        if(annotationCount == 0){
            throw new BusinessException(ERROR,"数据导出实体类不支持导出,请检查是否存在注解@EnableExportField");
        }
        // 默认的，未添加alias的属性也会写出，如果想只写出加了别名的字段，可以调用此方法排除之
        writer.setOnlyAlias(true);
    }

    /**
     * 自适应宽度
     *
     * @param sheet sheet
     * @param size  因为for循环从0开始，size值为 列数-1
     */
    private static void setAutoColumnSize(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum <= size; columnNum++) {
            sheet.autoSizeColumn(columnNum);
        }
    }


    /**
     * 导出 Excel
     * @param response HttpServletResponse
     * @param dataList 需要导出的数据
     * @param clazz 导出数据的clazz类型
     * @param exportTitle 当该参数不为空则替换默认的标题
     */
    private static void exportXlsExcel(HttpServletResponse response, Collection<?> dataList, Class<?> clazz, String exportTitle){
        // 创建一个Excel工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一个sheet表
        HSSFSheet hssfsheet =  workbook.createSheet();
        // 设置默认行高
        hssfsheet.setDefaultRowHeight( ( short )(20*20) );
        //检查当前class是否允许导出
        if(clazz.isAnnotationPresent(EnableExport.class)) {
            EnableExport export = clazz.getAnnotation(EnableExport.class);
            //获取所有标题名称
            List<String> colNames =new ArrayList<>();
            //获取所有标题的背景颜色
            List<ColorEnum> colors =new ArrayList<>();
            //所有允许导出的字段
            List<Field> fieldList = new ArrayList<>();
            for(Field field : clazz.getDeclaredFields()){
                if(field.isAnnotationPresent(EnableExportField.class)){
                    EnableExportField enableExportField = field.getAnnotation(EnableExportField.class);
                    colNames.add(enableExportField.colName());
                    colors.add(enableExportField.cellColor());
                    fieldList.add(field);
                }
            }
            //设置每列的宽度
            for(int i=0;i<fieldList.size();i++){
                Field field = fieldList.get(i);
                hssfsheet.setColumnWidth(i,field.getAnnotation(EnableExportField.class).colWidth()*20);
            }
            HSSFRow hssfRow;
            //绘制表头以及菜单
            String fileName = export.fileName();
            if(StringUtils.isBlank(fileName)){
                fileName = String.valueOf(System.currentTimeMillis());
            }
            if(StringUtils.isNotBlank(exportTitle)){
                fileName = exportTitle;
            }
            //绘制标题
            createTitle(workbook, hssfsheet, colNames.size()-1 ,fileName,export.cellColor());
            //创建标题行（表头）
            createHeadRow(workbook, hssfsheet,colNames,colors);
            try {
                //表格样式
                HSSFCellStyle cellStyle= getBasicCellStyle(workbook);
                //插入内容
                int i=0;
                for (Object obj : dataList) {
                    hssfRow = hssfsheet.createRow(i + 2);
                    //设置每列的宽度
                    //此处设置j=-1 ：添加一列，序号列
                    for(int j=0;j<fieldList.size();j++){
                        Field field = fieldList.get(j);
                        field.setAccessible(true);
                        Object value = field.get(obj);
                        EnableExportField enableExportField = field.getAnnotation(EnableExportField.class);
                        String getMethodName = enableExportField.useGetMethod();
                        if(!"".equals( getMethodName )){
                            Method  method = clazz.getMethod(getMethodName, field.getType());
                            method.setAccessible(true);
                            value = method.invoke(obj, value);
                        }
                        // EnableExportField 中 dateFormat 属性不为空表示此属性需要日期格式化
                        if (StringUtils.isNotEmpty(enableExportField.dateFormat())) {
                            if ( value != null ) {
                                if(value instanceof LocalDateTime){
                                    value = DateUtil.datetimeFormat((LocalDateTime) value, enableExportField.dateFormat());
                                }
                                if(value instanceof Date){
                                    value = DateUtil.dateFormat((Date)value,enableExportField.dateFormat());
                                }
                            }
                        }
                        // EnableExportField 中 replace 属性不为空表示此属性需要格式转换
                        for (String replaceText : enableExportField.replace()) {
                            // 注解自定义规则就是根据下划线进行分隔对象
                            String[] keys = replaceText.split(StringPool.UNDERSCORE);
                            if (keys.length != 2) {
                                throw new BusinessException(ERROR,"replace关键字规格不正确");
                            }
                            assert value != null;
                            if (keys[0].equals(value.toString())) {
                                value = keys[1];
                            }
                        }
                        // 设置单元格value值
                        setCellValue(value==null ? "无" : value, hssfRow, cellStyle, j);
                    }
                    i++;
                }
                // 文件名url
                String fileNameUrl;
                try {
                    fileNameUrl = URLEncoder.encode(fileName + XLS.getExt(), "UTF-8");
                } catch (UnsupportedEncodingException e) {
                    throw new BusinessException(ERROR,"文件名称Url编码异常");
                }
                response.setHeader(RESPONSE_HEADER_NAME, RESPONSE_HEADER_VALUE + fileNameUrl + StringPool.SEMICOLON+
                        // 处理中文文件名乱码
                        "filename*=utf-8''"+fileNameUrl);
                response.setContentType(RESPONSE_CONTENT_TYPE_XLS);
                // 创建输出流，向工作簿写入文件
                try (OutputStream outputStream = response.getOutputStream()){
                    workbook.write(outputStream);
                    outputStream.flush();
                }catch (Exception e) {
                    throw new BusinessException(ERROR,"IO操作异常："+e.getMessage());
                }
            } catch (IllegalAccessException e ) {
                throw new BusinessException(ERROR,"非法访问异常："+e.getMessage());
            } catch (NoSuchMethodException e) {
                throw new BusinessException(ERROR,"方法未找到异常："+e.getMessage());
            }catch (InvocationTargetException e) {
                throw new BusinessException(ERROR,"调用目标方法异常："+e.getMessage());
            }
        }
    }



    /**
     * 根据版本获取excel对象
     */
    public static Workbook getWork(String url, InputStream inputStream) throws IOException {
        Workbook wb;
        String str = url.substring(url.lastIndexOf("."));
        if (XLS.getExt().equals(str)) {
            // 2003-
            wb = new HSSFWorkbook(inputStream);
        } else if (XLSX.getExt().equals(str)) {
            // 2007+
            wb = new XSSFWorkbook(inputStream);
        } else {
            throw new BusinessException(ERROR,"excel文件格式有误!");
        }
        return wb;
    }


    public static Workbook getWorkBookFromMultipartFile(MultipartFile multipartFile) throws Exception {
        Workbook work = null;
        try {
            work = ExcelUtil.getWork(Objects.requireNonNull(multipartFile.getOriginalFilename()), multipartFile.getInputStream());
        } catch (IOException e) {
            throw new Exception("获取excel表对象异常！");
        } finally {
            if (null != work) {
                work.close();
            }
        }
        return work;
    }


    @SuppressWarnings("unchecked")
    private static <T> T convertObject(Row row,Map<Integer, String> propertyNameMap,Class<? extends T> type) {
        short num = row.getLastCellNum();
        log.info("单元格数量为：" + num);
        Object bean = Map.class.isAssignableFrom(type) ? new HashMap<>(16) : ReflectionUtil.instance(type.getName());
        //获取每一列值
        for (int j = 0; j < row.getLastCellNum(); j++) {
            //获取每一行的单元格值
            Cell cell = row.getCell(j);
            //单元格转换成String类型
            Object value = getCellValue(cell);
            if(value != null){
                // 属性名称
                String propertyName = propertyNameMap.get(j);
                // 调用setter方法
                try{
                    ReflectionUtil.setFieldValue(bean,propertyName,value);
                }catch (Exception e){
                    log.error("属性赋值异常:{}",e.getMessage());
                    log.error("数据格式异常:第{}行，第{}列",row.getRowNum(),j+1);
                }
            }
        }
        log.info("excel表格行信息为：" + row);
        return (T)bean;
    }


    /**
     * 获取属性名称映射map
     * @param clazz class
     * @return map
     */
    private static Map<Integer,String> getPropertyNameMap(Class<?> clazz){
        Map<Integer,String> propertyNameMap = new HashMap<>(16);
        Field[] fields = clazz.getDeclaredFields();
        if(fields.length == 0){
            throw new BusinessException(ERROR,"当前类对象属性值为空");
        }
        Arrays.stream(fields).forEach(field -> {
            // 获取实体属性导入下标
            ImportIndex importIndex = field.getAnnotation(ImportIndex.class);
            if(importIndex != null){
                propertyNameMap.put(importIndex.index(),field.getName());
            }
        });
        return propertyNameMap;
    }


    /**
     * 从文件中获取excel对象
     * @param file excel文件
     * @return excel对象
     * @throws BusinessException 解析excel文件异常时抛出的业务异常
     */
    private static Workbook getWorkbook(MultipartFile file){
        //获取excel对象
        Workbook workbook;
        try {
            workbook = getWorkBookFromMultipartFile(file);
        } catch (Exception e) {
            log.error("解析excel文件异常",e);
            throw new BusinessException(ERROR,"解析excel文件异常");
        }
        return workbook;
    }



    /**
     * 获取单元格的值
     * @param cell 单元格
     */
    private static Object getCellValue(Cell cell) {
        Object result = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case STRING:
                    result = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    // 在excel里,日期也是数字,在此要进行判断
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = cell.getDateCellValue();
                        result = sdf.format(date);
                    } else {
                        // 数字也按照字符串类型获取值
                        double d = cell.getNumericCellValue();
                        result = NUMBER_FORMAT.format(d);
                    }
                    break;
                case BOOLEAN:
                    // 布尔
                    result = cell.getBooleanCellValue();
                    break;
                case FORMULA:
                    // 公式
                    result = cell.getCellFormula();
                    break;
                case ERROR:
                    result = cell.getErrorCellValue();
                    break;
                case BLANK:
                    // null
                    break;
                default:
                    break;
            }
        }
        return result;
    }


    /**
     * 获取一个基本的带边框的单元格
     * @param workbook 工作簿对象
     * @return 带边框的单元格样式
     */
    private static HSSFCellStyle getBasicCellStyle(HSSFWorkbook workbook){
        HSSFCellStyle hssfcellstyle = workbook.createCellStyle();
        hssfcellstyle.setBorderLeft(BorderStyle.THIN);
        hssfcellstyle.setBorderBottom(BorderStyle.THIN);
        hssfcellstyle.setBorderRight(BorderStyle.THIN);
        hssfcellstyle.setBorderTop(BorderStyle.THIN);
        hssfcellstyle.setAlignment(HorizontalAlignment.CENTER);
        hssfcellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
        hssfcellstyle.setWrapText(true);
        return hssfcellstyle;
    }

    /**
     * 获取标题单元格样式
     *
     * @param workbook 工作簿对象
     * @param color 标题颜色枚举
     * @return 标题单元格样式
     */
    private static HSSFCellStyle getTitleCellStyle(HSSFWorkbook workbook,ColorEnum color){
        HSSFCellStyle hssfcellstyle =  getBasicCellStyle(workbook);
        hssfcellstyle.setFillForegroundColor(color.getIndex());
        // 设置背景色
        hssfcellstyle.setFillPattern(SOLID_FOREGROUND);
        return hssfcellstyle;
    }

    /**
     * 创建一个跨列的标题行
     */
    private static void createTitle(HSSFWorkbook workbook,
                                    HSSFSheet hssfsheet,
                                    int allColNum,
                                    String title,
                                    ColorEnum color){
        //在sheet里增加合并单元格
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, allColNum);
        hssfsheet.addMergedRegion(cra);
        // 使用RegionUtil类为合并后的单元格添加边框
        // 下边框
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, hssfsheet);
        // 左边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, hssfsheet);
        // 右边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, hssfsheet);
        // 上边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, hssfsheet);
        //设置表头
        HSSFRow hssfRow = hssfsheet.getRow(0);
        HSSFCell hssfcell = hssfRow.getCell(0);
        hssfcell.setCellStyle( getTitleCellStyle(workbook,color));
        hssfcell.setCellType(CellType.STRING);
        hssfcell.setCellValue(title);
    }

    /**
     * 创建表头行
     */
    private static void createHeadRow(HSSFWorkbook workbook,
                                      HSSFSheet hssfsheet,
                                      List<String> colNames,
                                      List<ColorEnum> colors){
        //插入标题行
        HSSFRow hssfRow = hssfsheet.createRow(1);
        for (int i = 0; i < colNames.size(); i++) {
            HSSFCell hssfcell = hssfRow.createCell(i);
            hssfcell.setCellStyle(getTitleCellStyle(workbook,colors.get(i)));
            hssfcell.setCellType(CellType.STRING);
            hssfcell.setCellValue(colNames.get(i));
        }
    }


    /**
     * 设置单元格的值
     */
    private static void setCellValue(Object value, HSSFRow hssfRow, CellStyle cellStyle, int cellIndex) {
        String valueStr = String.valueOf(value);
        HSSFCell hssfcell = hssfRow.createCell(cellIndex);
        hssfcell.setCellStyle(cellStyle);
        hssfcell.setCellType(CellType.STRING);
        hssfcell.setCellValue(valueStr);
    }

}
